package org.bigdata.framework.admin.web.util;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import jxl.Cell;
import jxl.Sheet;
import jxl.read.biff.BiffException;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;
public class ExcelMore {
	/**
	 * 大量
	 * @param path
	 * @param excelMap
	 * @param flag
	 */
	public static void writeExcelListMore(String path,HashMap<String, List<HashMap<String, Object>>> excelMap,int flag){
		try {   
			// 创建Excel工作薄   
			File file = new File(path);
			SXSSFWorkbook workbook = null;
			workbook = new SXSSFWorkbook();
			CellStyle normalCellStylet = getNormalCellStylet(workbook);
			CellStyle normalCellStyle = getNormalCellStyle(workbook);
				for(String key:excelMap.keySet()){
					// 添加第一个工作表并设置第一个Sheet的名字   
					SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(key);
					SXSSFRow row = null;
					SXSSFCell cell = null;
					SXSSFRow header = null;
					header = (SXSSFRow) sheet.createRow(0);
					int i = 0;
					for (String k : excelMap.get(key).get(0).keySet()){ 
						cell = (SXSSFCell) header.createCell(i);
						cell.setCellStyle(normalCellStylet);
						cell.setCellValue(k);
						if(i>0){
						sheet.autoSizeColumn(i);
						}
						i++;
					}
					int startRow = 1;
					for (Map<String, Object> m : excelMap.get(key)){  
						row = (SXSSFRow) sheet.createRow(startRow++);
						int j = 0;
						for (String k : m.keySet()){
							cell = (SXSSFCell) row.createCell(j++);
							cell.setCellStyle(normalCellStyle);
							cell.setCellValue(m.get(k).toString());
						}
					}
				}
		OutputStream stream = new FileOutputStream(file);
		workbook.write(stream);
		stream.flush();
		stream.close();
	  
		} catch (Exception e) {    
			e.printStackTrace();   
		}   
	}

	public static CellStyle getNormalCellStylet(SXSSFWorkbook workbook) {
		CellStyle normalCellStyle = workbook.createCellStyle();
		
		Font normalFont = workbook.createFont();// 设置字体大小
		normalFont.setFontName("宋体");// 设置字体名称
		normalFont.setFontHeightInPoints((short) 10);// 设置字体大小
		normalFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体加粗
		normalCellStyle.setFont(normalFont);// 设置单元格字体
		
		normalCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 设置上边框
		normalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 设置下边框
		normalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 设置左边框
		normalCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 设置右边框
		
		normalCellStyle.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置上边框颜色
		normalCellStyle.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置下边框颜色
		normalCellStyle.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置左边框颜色
		normalCellStyle.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置右边框颜色
		
		normalCellStyle.setFillBackgroundColor(HSSFColor.WHITE.index);// 设置背景色
		normalCellStyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置前景色
		normalCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充模式
		
		normalCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置水平对齐方式
		//normalCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置水平对齐方式
		normalCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置垂直对齐方式
		normalCellStyle.setDataFormat(workbook.createDataFormat().getFormat("@"));// 设置单元格为文本格式
		// normalCellStyle.setWrapText(true);// 设置自动换行，当文本中出现换行符时
		
		return normalCellStyle;
	}
	public static CellStyle getNormalCellStyle(SXSSFWorkbook workbook) {
		CellStyle normalCellStyle = workbook.createCellStyle();
		
		Font normalFont = workbook.createFont();// 设置字体大小
		normalFont.setFontName("宋体");// 设置字体名称
		normalFont.setFontHeightInPoints((short) 10);// 设置字体大小
		//normalFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体加粗
		normalCellStyle.setFont(normalFont);// 设置单元格字体
		
		normalCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 设置上边框
		normalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 设置下边框
		normalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 设置左边框
		normalCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 设置右边框
		
		normalCellStyle.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置上边框颜色
		normalCellStyle.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置下边框颜色
		normalCellStyle.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置左边框颜色
		normalCellStyle.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);// 设置右边框颜色
		
		normalCellStyle.setFillBackgroundColor(HSSFColor.WHITE.index);// 设置背景色
		normalCellStyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置前景色
		normalCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充模式
		
		normalCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置水平对齐方式
		//normalCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置水平对齐方式
		normalCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置垂直对齐方式
		normalCellStyle.setDataFormat(workbook.createDataFormat().getFormat("@"));// 设置单元格为文本格式
		// normalCellStyle.setWrapText(true);// 设置自动换行，当文本中出现换行符时
		
		return normalCellStyle;
	}

	public static List<Map<String, Object>> readExcel(String inPath,String sheetName) throws Exception{
		List<Map<String, Object>> readListMap = new ArrayList<Map<String,Object>>();
		if(inPath.endsWith(".xls")){//excel2003
			readListMap = readExcel2003(inPath, sheetName);
		}else if(inPath.endsWith(".xlsx")){//excel2007
			readListMap = readExcel2007(inPath, sheetName);
		}
		return readListMap;
	}
	public static List<Map<String, Object>> readExcel2003(String inPath,String sheetName) throws Exception {
		List<Map<String, Object>> readListMap = new ArrayList<Map<String,Object>>();
		HSSFWorkbook wb = getHSSFWork(inPath);
		int numberOfSheets = wb.getNumberOfSheets();
		if(sheetName == null || sheetName.equals("")){//没有指定sheetName，则全部倒进去
			for (int i = 0; i < numberOfSheets; i++) {
				readListMap.addAll(getReadListMap(wb, wb.getSheetAt(i).getSheetName()));
			}
		}else{//指定sheetName，则只导入sheet为sheetName页
			readListMap = getReadListMap(wb, sheetName);
		}
		return readListMap;
	}
	public static List<Map<String, Object>> readExcel2007(String inPath,String sheetName) throws Exception {
		List<Map<String, Object>> readListMap = new ArrayList<Map<String,Object>>();
		XSSFWorkbook wb = getXSSFWork(inPath);
		int numberOfSheets = wb.getNumberOfSheets();
		if(sheetName == null || sheetName.equals("")){//没有指定sheetName，则全部倒进去
			for (int i = 0; i < numberOfSheets; i++) {
				readListMap.addAll(getReadListMap(wb, wb.getSheetAt(i).getSheetName()));
			}
		}else{//指定sheetName，则只导入sheet为sheetName页
			readListMap = getReadListMap(wb, sheetName);
		}
		return readListMap;
	}
	public static HSSFWorkbook getHSSFWork(String inPath) throws FileNotFoundException{
		InputStream is = new FileInputStream(inPath);
		HSSFWorkbook wb = null;
		POIFSFileSystem fs = null;
		try {
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}

	public static XSSFWorkbook getXSSFWork(String inPath) throws FileNotFoundException{
		InputStream is = new FileInputStream(inPath);
		XSSFWorkbook wb = null;
		try {
			wb = new XSSFWorkbook(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}
	public static List<Map<String, Object>> getReadListMap(HSSFWorkbook wb,String sheetName) throws Exception{
		List<Map<String, Object>> listRow = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapRow = new HashMap<String, Object>();
		
		HSSFSheet sheet = wb.getSheet(sheetName);
		String[] columnName = getColumnName(wb, sheetName);
		int rowNum = getRowNum(wb, sheetName);
		int columnNum = getColumnNum(wb, sheetName);
		for (int i = 1; i < rowNum+1; i++) {
			HSSFRow row = sheet.getRow(i);
			mapRow = new HashMap<String, Object>();
			for (int j = 0; j < columnNum; j++) {
				mapRow.put(columnName[j], getCellFormatValue(row.getCell(j)));
			}
			listRow.add(mapRow);
		}
		return listRow;
	}

	public static List<Map<String, Object>> getReadListMap(XSSFWorkbook wb,String sheetName) throws Exception{
		List<Map<String, Object>> listRow = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapRow = new HashMap<String, Object>();
		XSSFSheet sheet = wb.getSheet(sheetName);
		String[] columnName = getColumnName(wb, sheetName);
		int rowNum = getRowNum(wb, sheetName);
		int columnNum = getColumnNum(wb, sheetName);
		for (int i = 1; i < rowNum+1; i++) {
			XSSFRow row = sheet.getRow(i);
			mapRow = new HashMap<String, Object>();
			for (int j = 0; j < columnNum; j++) {
				mapRow.put(columnName[j], getCellFormatValue(row.getCell(j)));
			}
			listRow.add(mapRow);
		}
		return listRow;
	}
	/**
	 * Excel2003获取行数
	 * @param wb
	 * @param sheetName
	 * @return
	 */
	public static int getRowNum(HSSFWorkbook wb,String sheetName){
		HSSFSheet sheet = wb.getSheet(sheetName);
		return sheet.getLastRowNum();
	}

	public static int getRowNum(XSSFWorkbook wb,String sheetName){
		XSSFSheet sheet = wb.getSheet(sheetName);
		return sheet.getLastRowNum();
	}

	public static int getColumnNum(HSSFWorkbook wb,String sheetName){
		HSSFSheet sheet = wb.getSheet(sheetName);
		HSSFRow row = sheet.getRow(0);
		if(row==null){
			return 0;
		}
		return row.getPhysicalNumberOfCells();
	}

	public static int getColumnNum(XSSFWorkbook wb,String sheetName){
		XSSFSheet sheet = wb.getSheet(sheetName);
		XSSFRow row = sheet.getRow(0);
		if(row  == null ){
			return 0;
		}
		return row.getPhysicalNumberOfCells();
	}
	public static String getCellFormatValue(HSSFCell cell) {
		String cellvalue = "";
		if (cell != null) {
			switch (cell.getCellType()) {// 判断当前Cell的Type
			case HSSFCell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_FORMULA: {// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式
					// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();
					// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat(
							"yyyy-MM-dd");
					cellvalue = sdf.format(date);
				} else { // 如果是纯数字
					cellvalue = String.valueOf(cell.getNumericCellValue()); // 取得当前Cell的数值
					if(cellvalue.endsWith(".0")){
						cellvalue=cellvalue.substring(0, cellvalue.length()-2);
					}
				}
				break;
			}
			case XSSFCell.CELL_TYPE_STRING:// 如果当前Cell的Type为String
				cellvalue = cell.getRichStringCellValue().getString();// 取得当前的Cell字符串
				break;
			default: // 默认的Cell值
				cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}
	/**
	 * Excel2007类型转换
	 * @param cell
	 * @return
	 */
	public static String getCellFormatValue(XSSFCell cell) {
		String cellvalue = "";
		if (cell != null) {
			switch (cell.getCellType()) {// 判断当前Cell的Type
			case XSSFCell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC
			case XSSFCell.CELL_TYPE_FORMULA: {// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式
					// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();
					// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat(
							"yyyy-MM-dd");
					cellvalue = sdf.format(date);
				} else { // 如果是纯数字
					cellvalue = String.valueOf(cell.getNumericCellValue()); // 取得当前Cell的数值
					if(cellvalue.endsWith(".0")){
						cellvalue=cellvalue.substring(0, cellvalue.length()-2);
					}
				}
				break;
			}
			case XSSFCell.CELL_TYPE_STRING:// 如果当前Cell的Type为String
				cellvalue = cell.getRichStringCellValue().getString();// 取得当前的Cell字符串
				break;
			default: // 默认的Cell值
				cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}
	public static String[] getColumnName(HSSFWorkbook wb,String sheetName) throws Exception{
		HSSFSheet sheet = wb.getSheet(sheetName);
		HSSFRow columnName = sheet.getRow(0);
		if(columnName==null){
			return new String[0];
		}
		int rowNum = columnName.getLastCellNum();
		String []columnNames=new String[rowNum];
		for (int i = 0; i < rowNum; i++) {
			columnNames[i]=columnName.getCell(i).getStringCellValue();
		}
		return columnNames;
	}
	/**
	 * 获取excel2007列名称
	 * @param inPath
	 * @param sheetName
	 * @return
	 * @throws Exception
	 */
	public static String[] getColumnName(XSSFWorkbook wb,String sheetName) throws Exception{
		XSSFSheet sheet = wb.getSheet(sheetName);
		XSSFRow columnName = sheet.getRow(0);
		if(columnName == null){
			return new String[0];
		}
		int rowNum = columnName.getLastCellNum();
		String []columnNames=new String[rowNum];
		for (int i = 0; i < rowNum; i++) {
			columnNames[i]=columnName.getCell(i).getStringCellValue();
		}
		return columnNames;
	}
}
